import pymysql
import logging
from RegionScrapy import settings
from RegionScrapy.items.RegionItem import RegionItem


class RegionPipeline(object):

    def __init__(self):
        self.connect = pymysql.connect(
            host=settings.MYSQL_HOST,
            db=settings.MYSQL_DBNAME,
            user=settings.MYSQL_USER,
            passwd=settings.MYSQL_PASSWD,
            charset='utf8',
            use_unicode=True)
        self.cursor = self.connect.cursor()

    def open_spider(self, spider):
        pass

    def close_spider(self, spider):
        self.cursor.close()
        self.connect.close()

    def process_item(self, item, spider):
        try:
            if not self.exist(item):
                self.insert(item)
            else:
                self.update(item)
        except Exception as ex:
            logging.log(ex)
            self.connect.rollback()
        return item

    def exist(self, item):
        sql = """SELECT code FROM `arthur`.`region` r WHERE r.`code` = %s"""
        self.cursor.execute(sql, (item.get("code", None)))
        ret = self.cursor.fetchone()

        if ret:
            print(item.get("name", None) + " 已存在")
            print(item)
            return True

        return False

    def insert(self, item):
        sql = """INSERT INTO `region`(`code`,`pcode`,`name`)\
VALUES(%s,%s,%s)"""

        self.cursor.execute(sql, (
            item.get("code", None),
            item.get("pcode", None),
            item.get("name", None)))
        self.connect.commit()

    def update(self, item):
        sql = """UPDATE `region` SET `code` = %s, `pcode` = %s, `name` = %s WHERE `code` = %s"""

        self.cursor.execute(sql, (
            item.get("code", None), item.get("pcode", None), item.get("name", None), item.get("code", None)))
        self.connect.commit()
